﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data;
using System.Data.SqlClient;
using System.Data.OleDb;
using System.IO;
using System.Configuration;

namespace DATN.ImportExcel
{
    public partial class ImportProduces : System.Web.UI.Page
    {
        private string cnnString;
        SqlConnection cnn;
        SqlCommand cmd;
        OleDbCommand ocmd;
        OleDbConnection oconn;

        protected void Page_Load(object sender, EventArgs e)
        {
            if (!IsPostBack)
            {
                if (Session["Us_Id"] != null)
                {
                    //Do something here!
                }
                else
                {
                    Response.Redirect("../Account/Login.aspx");
                }
                Validate();
            }
        }
        //Click vào button Import
        protected void Bt_Import_Click(object sender, EventArgs e)
        {
            if ((MyFile.PostedFile != null) && (MyFile.PostedFile.ContentLength > 0))
            {
                String ext = Path.GetExtension(MyFile.FileName);
                string fn = System.IO.Path.GetFileName(MyFile.PostedFile.FileName);
                string SaveLocation = Server.MapPath("..\\UploadFile") + "\\" + fn;
                try
                {
                    if ((MyFile.HasFile == false) || (MyFile.PostedFile.ContentLength > 1000000))
                    {
                        Lb_1.Visible = true;
                        Lb_1.Text = "Vui lòng chọn ảnh có kích thước dưới 1MB";
                    }
                    else
                    {
                        if (ext == ".xls" || ext == ".xlsx")
                        {
                            MyFile.PostedFile.SaveAs(SaveLocation);
                        }
                    }
                }
                catch (Exception ex)
                {
                    Lb_2.Text = "Lỗi Upload File: " + ex.Message;
                }
            }
            else
            {
                Lb_2.Text = "Chưa chọn file tải lên!.";
            }

            //Đọc file Excel và đổ vào database:
            ReadExcel();
        }

        //Đọc file Excel
        public void ReadExcel()
        {
            string Extension = Path.GetExtension(MyFile.PostedFile.FileName);
            string path = MyFile.PostedFile.FileName;
            string filePath = Path.GetFullPath(MyFile.FileName);

            String strConn = @"Provider = Microsoft.ACE.OLEDB.12.0;" + @"Data Source=" + Server.MapPath("..\\UploadFile") + "\\" +
                                 MyFile.FileName + @";Extended Properties='Excel 12.0; HDR=Yes;'";

            String strConn1 = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + Server.MapPath("..\\UploadFile") + "\\" +
                                 MyFile.FileName + "; Extended Properties=Excel 8.0";
            string conStr = "";
            switch (Extension)
            {
                case ".xls": //Excel 97-03
                    conStr = strConn1;
                    break;
                case ".xlsx": //Excel 07
                    conStr = strConn;
                    break;
            }

            try
            {
                oconn = new OleDbConnection(conStr);
                ocmd = new OleDbCommand("select * from [Sheet1$]", oconn);
                oconn.Open();  //Here [Sheet1$] is the name of the sheet in the Excel file where the data is present
                OleDbDataReader odr = ocmd.ExecuteReader();
                string EmpName = "";
                string EmpDescribe = "";
                string EmpPrice = "";

                while (odr.Read())
                {
                    EmpName = valid(odr, 0);
                    EmpDescribe = valid(odr, 1);
                    EmpPrice = valid(odr, 2);

                    insertdataintosql(EmpName, EmpDescribe, EmpPrice);
                }

                Lb_1.Text = "Thành Công";
                oconn.Close();
                //Nhập Link đến sp vào đây: ****
                //Response.Redirect("../mds.OpportunityManagement/IndexOM.aspx");
            }
            catch (Exception ex)
            {
                oconn.Close();
                Lb_1.Text = "Lỗi Khi Upload File : " + ex;
            }
        }


        //Bắt Validation Từ Chuỗi của File Excel
        protected string valid(OleDbDataReader myreader, int stval)
        {
            object val = myreader[stval];
            if (val != DBNull.Value)
                return val.ToString();
            else
                return Convert.ToString(0);
        }

        //Insert Vào database
        public void insertdataintosql(string EmpName, string EmpDescribe, string EmpPrice)
        {
            if (Session["Us_Id"] != null)
            {
                try
                {
                    int us_id = Convert.ToInt16(Session["Us_Id"].ToString());
                    cnnString = ConfigurationManager.ConnectionStrings["DATNconnection"].ToString();
                    cnn = new SqlConnection(cnnString);
                    cnn.Open();
                    SqlCommand cmd = new SqlCommand();

                    cmd.Connection = cnn;
                    cmd.CommandText = "INSERT INTO [dbo].[Products] ([Name], [Describe], [Price], [Us_Id])"
                                    +" VALUES (@Name, @Describe, @Price, @Us_Id)";


                    cmd.Parameters.Add("@Name", SqlDbType.NVarChar, 50).Value = EmpName;
                    cmd.Parameters.Add("@Describe", SqlDbType.NVarChar, 200).Value = EmpDescribe;
                    cmd.Parameters.Add("@Price", SqlDbType.Money).Value = EmpPrice;
                    cmd.Parameters.Add("@Us_Id", SqlDbType.Int).Value = us_id;

                    cmd.CommandType = CommandType.Text;
                    cmd.ExecuteNonQuery();
                    cnn.Close();
                }
                catch (Exception ex)
                {
                    Lb_2.Text = "Lỗi Với Dữ liệu trong File Excel" + ex;
                    cnn.Close();
                }
            }
        }
    }
}